﻿using CDMS.Common;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using CDMS.Entities;

namespace CDMS.DAL
{
    public class Customer_DAL
    {
        DBAccess db;
        public Customer_DAL()
        {
            db = new DBAccess();
        }

        public DataTable GetALL()
        {
            DataTable retVal = null;
            retVal = db.GetDataTable("Customer_GetALL");
            return retVal;
        }

        public DataTable GetAllByCustomerGroupID(int CustomerGroupID)
        {
            DataTable retVal = null;
            SqlParameter[] paramArray = new SqlParameter[] 
            { 
                new SqlParameter("CustomerGroupID",CustomerGroupID)
            };

            retVal = db.GetDataTable("Customer_GetAllByCustomerGroupID", paramArray);
            return retVal;
        }

        public DataTable Search(string KeyWord, string ColumnName, bool FromFirstChar)
        {
            DataTable retVal = null;
            SqlParameter[] paramArray = new SqlParameter[] 
            { 
                new SqlParameter("KeyWord",KeyWord),
                new SqlParameter("ColumnName",ColumnName),
                new SqlParameter("FromFirstChar",FromFirstChar),
            };

            retVal = db.GetDataTable("Customer_Search", paramArray);
            return retVal;
        }

        public Customer GetOne(int Customer_ID)
        {

            Customer retVal = null;
            DataTable dt = new DataTable();

            SqlParameter[] paramArray = new SqlParameter[] 
            { 
                new SqlParameter("Customer_ID",Customer_ID)
            };

            dt = db.GetDataTable("Customer_GetOne", paramArray);
            if (dt != null)
                if (dt.Rows.Count > 0)
                {
                    retVal = new Customer();
                    
                    retVal.Customer_ID = Convert.ToInt32(dt.Rows[0]["Customer_ID"]);
                    retVal.Customer_Code = dt.Rows[0]["Customer_Code"].ToString();
                    retVal.Customer_Name = dt.Rows[0]["Customer_Name"].ToString();
                    retVal.Customer_NameEN = dt.Rows[0]["Customer_NameEN"].ToString();
                    retVal.Customer_DOB = dt.Rows[0]["Customer_DOB"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(dt.Rows[0]["Customer_DOB"]);
                   
                    retVal.Customer_Address = dt.Rows[0]["Customer_Address"].ToString();
                    retVal.Customer_AddressShip = dt.Rows[0]["Customer_AddressShip"].ToString();
                    retVal.Customer_Phone = dt.Rows[0]["Customer_Phone"].ToString();
                    retVal.Customer_Mobile = dt.Rows[0]["Customer_Mobile"].ToString();
                    retVal.Customer_Email = dt.Rows[0]["Customer_Email"].ToString();
                    retVal.Customer_Contact = dt.Rows[0]["Customer_Contact"].ToString();
                    retVal.Customer_ContactPhone = dt.Rows[0]["Customer_ContactPhone"].ToString();
                    retVal.Customer_Position = Convert.ToInt32(dt.Rows[0]["Customer_Position"]);
                    retVal.Customer_Vocative = Convert.ToInt32(dt.Rows[0]["Customer_Vocative"]);
                    retVal.Customer_CustomerGroupID = Convert.ToInt32(dt.Rows[0]["Customer_CustomerGroupID"]);
                    retVal.Customer_CustomerTypeID = Convert.ToInt32(dt.Rows[0]["Customer_CustomerTypeID"]);
                    retVal.Customer_CityID = Convert.ToInt32(dt.Rows[0]["Customer_CityID"]);
                    retVal.Customer_SourceInfoID = Convert.ToInt32(dt.Rows[0]["Customer_SourceInfoID"]);
                    retVal.Customer_UserID = Convert.ToInt32(dt.Rows[0]["Customer_UserID"]);
                    retVal.Customer_NationlatyID = Convert.ToInt32(dt.Rows[0]["Customer_NationlatyID"]);
                    retVal.Customer_MarketSegmentID = Convert.ToInt32(dt.Rows[0]["Customer_MarketSegmentID"]);
                    retVal.Customer_RegistrationNo = dt.Rows[0]["Customer_RegistrationNo"].ToString();
                    retVal.Customer_RegistrationName = dt.Rows[0]["Customer_RegistrationName"].ToString();
                    retVal.Customer_RegistrationAddress = dt.Rows[0]["Customer_RegistrationAddress"].ToString();
                    retVal.Customer_TaxCode = dt.Rows[0]["Customer_TaxCode"].ToString();
                    retVal.Customer_AcceptEmail = Convert.ToBoolean(dt.Rows[0]["Customer_AcceptEmail"]);
                    retVal.Customer_Website = dt.Rows[0]["Customer_Website"].ToString();
                }
            return retVal;
        }

        public int Insert(Customer entity)
        {
            int retVal;
            SqlParameter pr = new SqlParameter();
            if (entity.Customer_DOB == DateTime.MinValue)
                pr = new SqlParameter("Customer_DOB", DBNull.Value);
            else
                pr = new SqlParameter("Customer_DOB", entity.Customer_DOB);
            SqlParameter[] paramArray = new SqlParameter[] 
            { 
                new SqlParameter("Customer_Code",entity.Customer_Code),
                new SqlParameter("Customer_Name",entity.Customer_Name),
                new SqlParameter("Customer_NameEN",entity.Customer_NameEN),
                pr,
                new SqlParameter("Customer_Address",entity.Customer_Address),
                new SqlParameter("Customer_AddressShip",entity.Customer_AddressShip),
                new SqlParameter("Customer_Phone",entity.Customer_Phone),
                new SqlParameter("Customer_Mobile",entity.Customer_Mobile),
                new SqlParameter("Customer_Email",entity.Customer_Email),
                new SqlParameter("Customer_Contact",entity.Customer_Contact),
                new SqlParameter("Customer_ContactPhone",entity.Customer_ContactPhone),
                new SqlParameter("Customer_Position",entity.Customer_Position),
                new SqlParameter("Customer_Vocative",entity.Customer_Vocative),
                new SqlParameter("Customer_CustomerGroupID",entity.Customer_CustomerGroupID),
                new SqlParameter("Customer_CustomerTypeID",entity.Customer_CustomerTypeID),
                new SqlParameter("Customer_CityID",entity.Customer_CityID),
                new SqlParameter("Customer_SourceInfoID",entity.Customer_SourceInfoID),
                new SqlParameter("Customer_UserID",entity.Customer_UserID),
                new SqlParameter("Customer_NationlatyID",entity.Customer_NationlatyID),
                new SqlParameter("Customer_MarketSegmentID",entity.Customer_MarketSegmentID),
                new SqlParameter("Customer_RegistrationNo",entity.Customer_RegistrationNo),
                new SqlParameter("Customer_RegistrationName",entity.Customer_RegistrationName),
                new SqlParameter("Customer_RegistrationAddress",entity.Customer_RegistrationAddress),
                new SqlParameter("Customer_TaxCode",entity.Customer_TaxCode),
                new SqlParameter("Customer_AcceptEmail",entity.Customer_AcceptEmail),
                new SqlParameter("Customer_Website",entity.Customer_Website),
            };

            retVal = Convert.ToInt32(db.ExecuteScalar("Customer_Insert", paramArray));

            return retVal;
        }
        public bool Update(Customer entity)
        {
            SqlParameter pr = new SqlParameter();
            if (entity.Customer_DOB == DateTime.MinValue)
                pr = new SqlParameter("Customer_DOB", DBNull.Value);
            else
                pr = new SqlParameter("Customer_DOB", entity.Customer_DOB);
            SqlParameter[] paramArray = new SqlParameter[] 
            { 
                new SqlParameter("Customer_ID",entity.Customer_ID),
                new SqlParameter("Customer_Code",entity.Customer_Code),
                new SqlParameter("Customer_Name",entity.Customer_Name),
                new SqlParameter("Customer_NameEN",entity.Customer_NameEN),
                pr,
                new SqlParameter("Customer_Address",entity.Customer_Address),
                new SqlParameter("Customer_AddressShip",entity.Customer_AddressShip),
                new SqlParameter("Customer_Phone",entity.Customer_Phone),
                new SqlParameter("Customer_Mobile",entity.Customer_Mobile),
                new SqlParameter("Customer_Email",entity.Customer_Email),
                new SqlParameter("Customer_Contact",entity.Customer_Contact),
                new SqlParameter("Customer_ContactPhone",entity.Customer_ContactPhone),
                new SqlParameter("Customer_Position",entity.Customer_Position),
                new SqlParameter("Customer_Vocative",entity.Customer_Vocative),
                new SqlParameter("Customer_CustomerGroupID",entity.Customer_CustomerGroupID),
                new SqlParameter("Customer_CustomerTypeID",entity.Customer_CustomerTypeID),
                new SqlParameter("Customer_CityID",entity.Customer_CityID),
                new SqlParameter("Customer_SourceInfoID",entity.Customer_SourceInfoID),
                new SqlParameter("Customer_UserID",entity.Customer_UserID),
                new SqlParameter("Customer_NationlatyID",entity.Customer_NationlatyID),
                new SqlParameter("Customer_MarketSegmentID",entity.Customer_MarketSegmentID),
                new SqlParameter("Customer_RegistrationNo",entity.Customer_RegistrationNo),
                new SqlParameter("Customer_RegistrationName",entity.Customer_RegistrationName),
                new SqlParameter("Customer_RegistrationAddress",entity.Customer_RegistrationAddress),
                new SqlParameter("Customer_TaxCode",entity.Customer_TaxCode),
                new SqlParameter("Customer_AcceptEmail",entity.Customer_AcceptEmail),
                new SqlParameter("Customer_Website",entity.Customer_Website),
            };


            return Convert.ToInt32(db.ExecuteNonQuery("Customer_Update", paramArray)) > 0;
        }

        public bool Exists(Customer entity)
        {
            SqlParameter[] paramArray = new SqlParameter[] 
            { 
                new SqlParameter("Customer_ID",entity.Customer_ID),
                new SqlParameter("Customer_Code",entity.Customer_Code)
                
            };


            return Convert.ToInt32(db.ExecuteScalar("Customer_Exists", paramArray)) > 0;
        }

        public bool Delete(int Customer_ID)
        {
            SqlParameter[] paramArray = new SqlParameter[] 
            { 
                new SqlParameter("Customer_ID",Customer_ID)
            };


            return Convert.ToInt32(db.ExecuteNonQuery("Customer_Delete", paramArray)) > 0;
        }
    }
}
